#delimit;
clear all;
set more off;

capture log using "03_Readin_Handcode_investments_and_Classify.log", replace;
/*******************************************************************************************/;
** INPUT: Digiitized (by hand) Investment Records for DAFs, CPI data; 
** OUTPUT: Tables and Figures; 
** LAST MODIFIED: JAG, 7/6/2021;
** RUN TIME: <5 minutes using Stata 15 on computer with 2 x 2.60GHz processors and 64GB RAM;  													      	
/*******************************************************************************************/;

** This is the investment file that contains digitized (by hand) data with the actual value of the investments from the 990 forms;
import excel "../Raw Data/PDF Copies of 990 Form/Completed Handcode/Securites Handcode.xlsx", sheet("Sheet1") firstrow;
keep if J!="";
gen sec_value=real(J);
** I checked these and they are do to spaces;
count if J!="" & sec_value==.;
format sec_value %12.0fc;
drop if sec_value==.;
sum sec_value, d;
drop J L K M N O;

/*CONVERT EVERYTHING INTO REAL TERMS USING CPI*/
sort year;
merge m:1 year using "../Raw Data/CPI/CPI Annual.dta";
keep if _m==3;
drop _m;

foreach x in sec_value {;
	replace `x'=(`x'/cpi2010)*100;
};
drop cpi2010;

** GOING THROUGH AND CLEANING UP THE SECURITY TYPE BASED ON THE DESCRIPTIONS; 
replace description_security=lower(trim(description_security));
rename description_security desc;

** COMMUNITY;
gen impact=regexm(desc,"impact");
replace impact=regexm(desc,"community") if impact==0;
replace impact=regexm(desc,"charit") if impact==0;
replace impact=regexm(desc,"foundation") if impact==0;
replace impact=regexm(desc,"programmatic") if impact==0;
replace impact=regexm(desc,"mission") if impact==0;
replace impact=regexm(desc,"hospital") if impact==0;
replace impact=regexm(desc,"investment in jail education solutions, inc.") if impact==0;
replace impact=regexm(desc,"investment in thinkcerca, inc.") if impact==0;
replace impact=regexm(desc,"investment in the paradigm project l3c") if impact==0;
replace impact=regexm(desc,"investment in livful, inc.") if impact==0;
replace impact=regexm(desc,"investment in co-tribute") if impact==0;
replace impact=regexm(desc,"investments - restricted") if impact==0;
replace impact=regexm(desc,"investment in turaco ltd") if impact==0;
replace impact=regexm(desc,"social inv") if impact==0;
** jensen is sustainable investment;
replace impact=regexm(desc,"jensen portfolio") if impact==0;
replace impact=regexm(desc,"piscassic greenway fund") if impact==0;
replace impact=regexm(desc,"easement stewardship fund") if impact==0;
replace impact=regexm(desc,"glenmede") if impact==0;
replace impact=regexm(desc,"diamond education") if impact==0;
replace impact=regexm(desc,"to supporting organizations") if impact==0;
** Altrushare is a good example of a firm that went under;
replace impact=regexm(desc,"altrushare") if impact==0;
replace impact=regexm(desc,"ecap network") if impact==0;
replace impact=regexm(desc,"scholarship") if impact==0;
replace impact=regexm(desc,"bio-logical capital") if impact==0;
replace impact=regexm(desc,"generation investments") if impact==0;
replace impact=regexm(desc,"town renaissance fund") if impact==0;
replace impact=regexm(desc,"green eq") if impact==0;
replace impact=regexm(desc,"esg") if impact==0;
replace impact=regexm(desc,"rsf social finance") if impact==0;
replace impact=regexm(desc,"silicon valley fund") if impact==0;
** Adding names of impact funds from various lists that RA found;
replace impact=regexm(desc,"encourage capital") if impact==0;
replace impact=regexm(desc,"fledge") if impact==0;
replace impact=regexm(desc,"lendable") if impact==0;
replace impact=regexm(desc,"patamar") if impact==0;
replace impact=regexm(desc,"vested world") if impact==0;
replace impact=regexm(desc,"arborview cpaital") if impact==0;
replace impact=regexm(desc,"bamboo") if impact==0;
replace impact=regexm(desc,"palladium") if impact==0;
replace impact=regexm(desc,"dbl partners") if impact==0;
replace impact=regexm(desc,"double bottom line") if impact==0;
replace impact=regexm(desc,"investisseurs") if impact==0;
replace impact=regexm(desc,"partenaires") if impact==0;
replace impact=regexm(desc,"prudential") if impact==0;
replace impact=regexm(desc,"pgim") if impact==0;
replace impact=regexm(desc,"361 infrastructure") if impact==0;
replace impact=regexm(desc,"adjuvant") if impact==0;
replace impact=regexm(desc,"advance global cap") if impact==0;
replace impact=regexm(desc,"athelia") if impact==0;
replace impact=regexm(desc,"capria") if impact==0;
replace impact=regexm(desc,"cim") if impact==0;
replace impact=regexm(desc,"community investment") if impact==0;
replace impact=regexm(desc,"ecosystem integrity") if impact==0;
replace impact=regexm(desc,"efm") if impact==0;
replace impact=regexm(desc,"global partnerships") if impact==0;
replace impact=regexm(desc,"equity trust") if impact==0;
replace impact=regexm(desc,"housing partnership") if impact==0;
replace impact=regexm(desc,"iroquis valley") if impact==0;
replace impact=regexm(desc,"kairos") if impact==0;
replace impact=regexm(desc,"kimc") if impact==0;
replace impact=regexm(desc,"microvest") if impact==0;
replace impact=regexm(desc,"north sky") if impact==0;
replace impact=regexm(desc,"quona") if impact==0;
replace impact=regexm(desc,"raven indigenous") if impact==0;
replace impact=regexm(desc,"raven cap") if impact==0;
replace impact=regexm(desc,"root cap") if impact==0;
replace impact=regexm(desc,"sjf") if impact==0;
replace impact=regexm(desc,"steelsky") if impact==0;
replace impact=regexm(desc,"sustain") if impact==0;
replace impact=regexm(desc,"turner impact") if impact==0;
replace impact=regexm(desc,"water equity") if impact==0;
replace impact=regexm(desc,"accion") if impact==0;
replace impact=regexm(desc,"aiim") if impact==0;
replace impact=regexm(desc,"alante") if impact==0;
replace impact=regexm(desc,"aqua-spark") if impact==0;
replace impact=regexm(desc,"aquaspark") if impact==0;
replace impact=regexm(desc,"better") if impact==0;
replace impact=regexm(desc,"calvert") if impact==0;
replace impact=regexm(desc,"capital impact") if impact==0;
replace impact=regexm(desc,"ciculate cap") if impact==0;
replace impact=regexm(desc,"drawdown fund") if impact==0;
replace impact=regexm(desc,"dwm") if impact==0;
replace impact=regexm(desc,"developing work markets") if impact==0;
replace impact=regexm(desc,"eco enterprises") if impact==0;
replace impact=regexm(desc,"elevar") if impact==0;
replace impact=regexm(desc,"finance in motion") if impact==0;
replace impact=regexm(desc,"full cycle") if impact==0;
replace impact=regexm(desc,"gawa cap") if impact==0;
replace impact=regexm(desc,"goodwell") if impact==0;
replace impact=regexm(desc,"hcap") if impact==0;
replace impact=regexm(desc,"illumen") if impact==0;
replace impact=regexm(desc,"incofin") if impact==0;
replace impact=regexm(desc,"inoks") if impact==0;
replace impact=regexm(desc,"kiva") if impact==0;
replace impact=regexm(desc,"leapfrog") if impact==0;
replace impact=regexm(desc,"liif") if impact==0;
replace impact=regexm(desc,"lisc") if impact==0;
replace impact=regexm(desc,"living cit") if impact==0;
replace impact=regexm(desc,"lyme") if impact==0;
replace impact=regexm(desc,"mdif") if impact==0;
replace impact=regexm(desc,"media development") if impact==0;
replace impact=regexm(desc,"medical credit") if impact==0;
replace impact=regexm(desc,"mission driven") if impact==0;
replace impact=regexm(desc,"mission-driven") if impact==0;
replace impact=regexm(desc,"cnote") if impact==0;
replace impact=regexm(desc,"prime impact") if impact==0;
replace impact=regexm(desc,"responsability") if impact==0;
replace impact=regexm(desc,"sarona") if impact==0;
replace impact=regexm(desc,"sds") if impact==0;
replace impact=regexm(desc,"seaf") if impact==0;
replace impact=regexm(desc,"impact engine") if impact==0;
replace impact=regexm(desc,"tsef") if impact==0;
replace impact=regexm(desc,"social entre") if impact==0;
replace impact=regexm(desc,"maycomb cap") if impact==0;
replace impact=regexm(desc,"unicef") if impact==0;
replace impact=regexm(desc,"vital cap") if impact==0;
replace impact=regexm(desc,"vox cap") if impact==0;
replace impact=regexm(desc,"women's world bank") if impact==0;
replace impact=regexm(desc,"gender") if impact==0;
replace impact=regexm(desc,"racial") if impact==0;
replace impact=regexm(desc,"race") if impact==0;
replace impact=regexm(desc,"justice") if impact==0;
replace impact=regexm(desc,"deetken") if impact==0;
replace impact=regexm(desc,"fair") if impact==0;
replace impact=regexm(desc,"greater des moines foundtion") if impact==0;
replace impact=regexm(desc,"interest in related organizations") if impact==0;
replace impact=regexm(desc,"webster/ccf inv accounts") if impact==0;
** First light is borderline, they invest in healthcare focused for boomers;
replace impact=regexm(desc,"first light focus") if impact==0;
replace impact=regexm(desc,"oak hill") if impact==0;
** per website is impact https://www.ciginvest.com/about/social-responsibility/;
replace impact=regexm(desc,"consolidated investment fund") if impact==0;
** website suggests positve activism https://www.firtree.com/Default.aspx;
replace impact=regexm(desc,"fir tree") if impact==0;
replace impact=regexm(desc,"investment at national park") if impact==0;
replace impact=regexm(desc,"investment in ambasssdor development group") if impact==0;

** HEDGE FUNDS;
gen hfund=regexm(desc,"hedge fund");
replace hfund=regexm(desc,"hedged eq") if hfund==0;
replace hfund=regexm(desc,"janus") if hfund==0;
replace hfund=regexm(desc,"aqr") if hfund==0;
replace hfund=regexm(desc,"bridgewater") if hfund==0;
replace hfund=regexm(desc,"two sigma") if hfund==0;
replace hfund=regexm(desc,"elliott") if hfund==0;
replace hfund=regexm(desc,"man group") if hfund==0;
replace hfund=regexm(desc,"short equity") if hfund==0;
replace hfund=regexm(desc,"multi-strategy") if hfund==0;
replace hfund=regexm(desc,"multi strategy") if hfund==0;
replace hfund=regexm(desc,"weatherlow") if hfund==0;
replace hfund=regexm(desc,"hilliard") if hfund==0;
replace hfund=regexm(desc,"jennison") if hfund==0;
replace hfund=regexm(desc,"hedged strategies") if hfund==0;
replace hfund=regexm(desc,"hedged funds") if hfund==0;
replace hfund=regexm(desc,"loomis sayles") if hfund==0;
replace hfund=regexm(desc,"strategic") if hfund==0;
replace hfund=regexm(desc,"hedged investors company") if hfund==0;
replace hfund=regexm(desc,"discovery global opportunities") if hfund==0;
replace hfund=regexm(desc,"hedge fds") if hfund==0;
replace hfund=regexm(desc,"abdiel qualified") if hfund==0;
replace hfund=regexm(desc,"absolute return strategies") if hfund==0;
replace hfund=regexm(desc,"absolute return funds") if hfund==0;
replace hfund=regexm(desc,"indus pacific opportunities fund") if hfund==0;
replace hfund=regexm(desc,"davidson kempner") if hfund==0;
replace hfund=regexm(desc,"david kempner") if hfund==0;
replace hfund=regexm(desc,"canyon") if hfund==0;
replace hfund=regexm(desc,"paloma international") if hfund==0;
replace hfund=regexm(desc,"hoplite") if hfund==0;
replace hfund=regexm(desc,"cevian capital") if hfund==0;
replace hfund=regexm(desc,"mason") if hfund==0;
replace hfund=regexm(desc,"forester partners") if hfund==0;
replace hfund=regexm(desc,"multiple strategy") if hfund==0;
replace hfund=regexm(desc,"aurora") if hfund==0;
replace hfund=regexm(desc,"millennium") if hfund==0;
replace hfund=regexm(desc,"ecm feeder") if hfund==0;
replace hfund=regexm(desc,"gobi concentrated") if hfund==0;
replace hfund=regexm(desc,"hengistbury") if hfund==0;
replace hfund=regexm(desc,"tybourne") if hfund==0;
replace hfund=regexm(desc,"feg ") if hfund==0;
replace hfund=regexm(desc,"offshore") if hfund==0;
replace hfund=regexm(desc,"brevan howard") if hfund==0;
replace hfund=regexm(desc,"hedgefund") if hfund==0;
replace hfund=regexm(desc,"dimensional") if hfund==0;
replace hfund=regexm(desc,"regiment capital") if hfund==0;
replace hfund=regexm(desc,"k2") if hfund==0;
replace hfund=regexm(desc,"eton park") if hfund==0;
replace hfund=regexm(desc,"bet asset vakye") if hfund==0;
replace hfund=regexm(desc,"mesirow fund of funds") if hfund==0;
replace hfund=regexm(desc,"pine grove fund of funds") if hfund==0;
replace hfund=regexm(desc,"nye ledge capital") if hfund==0;
replace hfund=regexm(desc,"hedge strategies") if hfund==0;
replace hfund=regexm(desc,"greenlight masters") if hfund==0;
replace hfund=regexm(desc,"brenner west") if hfund==0;
replace hfund=regexm(desc,"opportunistic funds") if hfund==0;
replace hfund=regexm(desc,"newport asia") if hfund==0;
replace hfund=regexm(desc,"arisaig global") if hfund==0;
replace hfund=regexm(desc,"maverick") if hfund==0;
replace hfund=regexm(desc,"och ziff") if hfund==0;
replace hfund=regexm(desc,"global off-shore fund") if hfund==0;
replace hfund=regexm(desc,"privately managed investment fund") if hfund==0;
replace hfund=regexm(desc,"berens global") if hfund==0;
replace hfund=regexm(desc,"magnitude") if hfund==0;
replace hfund=regexm(desc,"mw eureka fund") if hfund==0;
replace hfund=regexm(desc,"swiftcurrent") if hfund==0;
replace hfund=regexm(desc,"hudson bay") if hfund==0;
replace hfund=regexm(desc,"convexity") if hfund==0;
replace hfund=regexm(desc,"third point") if hfund==0;
replace hfund=regexm(desc,"highfields") if hfund==0;
replace hfund=regexm(desc,"steadfast international") if hfund==0;
replace hfund=regexm(desc,"high ground") if hfund==0;
replace hfund=regexm(desc,"absolute returns") if hfund==0;
replace hfund=regexm(desc,"marshall wace") if hfund==0;
replace hfund=regexm(desc,"long/short fund of funds") if hfund==0;

** FIXED INCOME;
gen bonds=regexm(desc,"fixed inc");
replace bonds=regexm(desc,"treasury") if bonds==0;
replace bonds=regexm(desc,"bond") if bonds==0;
replace bonds=regexm(desc,"note") if bonds==0;
replace bonds=regexm(desc,"bill") if bonds==0;
replace bonds=regexm(desc,"insur") if bonds==0;
replace bonds=regexm(desc,"life ins") if bonds==0;
replace bonds=regexm(desc,"floating") if bonds==0;
replace bonds=regexm(desc,"annuit") if bonds==0;
replace bonds=regexm(desc,"debt") if bonds==0;
replace bonds=regexm(desc,"income") if bonds==0;
replace bonds=regexm(desc,"credit") if bonds==0;
replace bonds=regexm(desc,"interest agree") if bonds==0;
replace bonds=regexm(desc,"bnd idx") if bonds==0;
replace bonds=regexm(desc,"bd f") if bonds==0;
replace bonds=regexm(desc,"global bd") if bonds==0;
replace bonds=regexm(desc,"loan") if bonds==0;
replace bonds=regexm(desc,"funded guarantee") if bonds==0;
replace bonds=regexm(desc,"duration") if bonds==0;
replace bonds=regexm(desc,"tips") if bonds==0;
replace bonds=regexm(desc,"high yield") if bonds==0;
replace bonds=regexm(desc,"invest grade") if bonds==0;
replace bonds=regexm(desc,"investment grade") if bonds==0;
replace bonds=regexm(desc,"preferred interest") if bonds==0;
replace bonds=regexm(desc,"inflation") if bonds==0;
replace bonds=regexm(desc,"%") if bonds==0;
replace bonds=regexm(desc,"fu sec sgrt bd") if bonds==0;
replace bonds=regexm(desc,"tax cert") if bonds==0;
replace bonds=regexm(desc,"beneficial interest") if bonds==0;
replace bonds=regexm(desc,"structured") if bonds==0;
replace bonds=regexm(desc,"government securities") if bonds==0;
replace bonds=regexm(desc,"intrm bnd") if bonds==0;
replace bonds=regexm(desc,"ishares iboxx") if bonds==0;
replace bonds=regexm(desc,"western asset core plus") if bonds==0;
replace bonds=regexm(desc,"wisdomtree barclay yield") if bonds==0;
replace bonds=regexm(desc,"metropolitan west total") if bonds==0;
replace bonds=regexm(desc,"pimco") if bonds==0;
replace bonds=regexm(desc,"vanguard fi sec shrt bd") if bonds==0;
replace bonds=regexm(desc,"obligation") if bonds==0;
replace bonds=regexm(desc,"interests") if bonds==0;
replace bonds=regexm(desc,"assets held by others") if bonds==0;
replace bonds=regexm(desc,"tcw total return") if bonds==0;
replace bonds=regexm(desc,"municipal")  if bonds==0;
replace bonds=regexm(desc,"annuity")  if bonds==0;
replace bonds=regexm(desc,"guggenheim total return bd")  if bonds==0;
replace bonds=regexm(desc,"pif high-yield fund")  if bonds==0;
replace bonds=regexm(desc,"barclays invt grade")  if bonds==0;

** PE;
gen pe=regexm(desc,"private eq");
replace pe=regexm(desc,"pe") if pe==0;
replace pe=regexm(desc,"privately held stock") if pe==0;
replace pe=regexm(desc,"privately held common stock") if pe==0;
replace pe=regexm(desc,"private capital") if pe==0;
replace pe=regexm(desc,"close-held") if pe==0;
replace pe=regexm(desc,"closely-held") if pe==0;
replace pe=regexm(desc,"closely held") if pe==0;
replace pe=regexm(desc,"closely held stock") if pe==0;
replace pe=regexm(desc,"closely-held stock") if pe==0;
replace pe=regexm(desc,"closely-held equity") if pe==0;
replace pe=regexm(desc,"closelu-held equity") if pe==0;
replace pe=regexm(desc,"closely held equity") if pe==0;
replace pe=regexm(desc,"llc") if pe==0;
replace pe=regexm(desc,"limited liability co") if pe==0;
replace pe=regexm(desc,"private co") if pe==0;
replace pe=regexm(desc,"subsidiar") if pe==0;
replace pe=regexm(desc,"feg private") if pe==0;
replace pe=regexm(desc,"private inv") if pe==0;
replace pe=regexm(desc,"non public") if pe==0;
replace pe=regexm(desc,"non-public") if pe==0;
replace pe=regexm(desc,"private eguity funds") if pe==0;
replace pe=regexm(desc,"sovereign capital") if pe==0;
replace pe=regexm(desc,"maestro conference") if pe==0;
replace pe=regexm(desc,"buyout funds") if pe==0;
replace pe=regexm(desc,"privately managed investments") if pe==0;
replace pe=regexm(desc,"non-exchange traded secunties") if pe==0;
replace pe=regexm(desc,"repurchase agreement") if pe==0;
replace pe=regexm(desc,"gryphon") if pe==0;
replace pe=regexm(desc,"vulcan") if pe==0;
replace pe=regexm(desc,"mondrian") if pe==0;
replace pe=regexm(desc,"blackstone") if pe==0;
replace pe=regexm(desc,"carlyle") if pe==0;
replace pe=regexm(desc,"kkr") if pe==0;
replace pe=regexm(desc,"tpg") if pe==0;
replace pe=regexm(desc,"warburg pincus") if pe==0;
replace pe=regexm(desc,"neuberger berman") if pe==0;
replace pe=regexm(desc,"dorchester capital") if pe==0;
replace pe=regexm(desc,"seneca capital") if pe==0;
replace pe=regexm(desc,"colchester") if pe==0;
replace pe=regexm(desc,"oaktree") if pe==0;
replace pe=regexm(desc,"stepstone") if pe==0;
replace pe=regexm(desc,"liquid limited") if pe==0;
replace pe=regexm(desc,"equity securities and funds, not publicly traded") if pe==0;
replace pe=regexm(desc,"investment in haley associates") if pe==0;
replace pe=regexm(desc,"privately held securities") if pe==0;
replace pe=regexm(desc,"private placement") if pe==0;
replace pe=regexm(desc,"theleme") if pe==0;
replace pe=regexm(desc,"investments in limited liability entities") if pe==0;
replace pe=regexm(desc,"reverse repurchase agreements") if pe==0;
replace pe=regexm(desc,"investment in subsdiaries") if pe==0;
replace pe=regexm(desc,"harbourvest funds") if pe==0;
replace pe=regexm(desc,"riverside") if pe==0;
replace pe=regexm(desc,"investment in other institutions") if pe==0;
replace pe=(desc=="pf") if pe==0;


** VC;
gen vc=regexm(desc,"venture cap");
replace vc=regexm(desc,"vc") if vc==0;
replace vc=regexm(desc,"venture") if vc==0;
replace vc=regexm(desc,"limited partnership") if vc==0;
replace vc=regexm(desc,"lp") if vc==0;
replace vc=regexm(desc,"partners, l") if vc==0;
replace vc=regexm(desc,"partnership") if vc==0;
replace vc=regexm(desc,"ltd. partner") if vc==0;
replace vc=regexm(desc,"limited partnerships") if vc==0;
replace vc=regexm(desc,"seed") if vc==0;
replace vc=regexm(desc,"angel") if vc==0;
replace vc=regexm(desc,"commonfund") if vc==0;
replace vc=regexm(desc,"common fund") if vc==0;
replace vc=regexm(desc,"commonfund") if vc==0;
replace vc=regexm(desc,"adage capital") if vc==0;
replace vc=regexm(desc,"unipolar technology inc.") if vc==0;
replace vc=regexm(desc,"investment in simplywin") if vc==0;
replace vc=regexm(desc,"investment in gli") if vc==0;
replace vc=regexm(desc,"investment in familywise digital inc") if vc==0;
replace vc=regexm(desc,"investment in nec austin") if vc==0;
replace vc=regexm(desc,"investment in pacific refrigeration parent") if vc==0;


** STOCKS;
gen stock=regexm(desc,"domestic eq");
replace stock=regexm(desc,"common stock") if stock==0;
replace stock=regexm(desc,"stock") if stock==0;
replace stock=regexm(desc,"public eq") if stock==0;
replace stock=regexm(desc,"foreign eq") if stock==0;
replace stock=regexm(desc,"global eq") if stock==0;
replace stock=regexm(desc,"international eq") if stock==0;
replace stock=regexm(desc,"intl eq") if stock==0;
replace stock=regexm(desc,"u.s. eq") if stock==0;
replace stock=regexm(desc,"us eq") if stock==0;
replace stock=regexm(desc,"mutual fund") if stock==0;
replace stock=regexm(desc,"mut fds") if stock==0;
replace stock=regexm(desc,"global market fund") if stock==0;
replace stock=regexm(desc,"equity fund") if stock==0;
replace stock=regexm(desc,"equities fund") if stock==0;
replace stock=regexm(desc,"exchange traded funds") if stock==0;
replace stock=regexm(desc,"etf") if stock==0;
replace stock=regexm(desc,"ameritrade") if stock==0;
replace stock=regexm(desc,"charles schwab") if stock==0;
replace stock=regexm(desc,"silchester") if stock==0;
replace stock=regexm(desc,"absolute return fund") if stock==0;
replace stock=regexm(desc,"long-term growth") if stock==0;
replace stock=regexm(desc,"growth pool") if stock==0;
replace stock=regexm(desc,"valu eq") if stock==0;
replace stock=regexm(desc,"value fund") if stock==0;
replace stock=regexm(desc,"merg grw") if stock==0;
replace stock=regexm(desc,"dev mkts") if stock==0;
replace stock=regexm(desc,"contrafund") if stock==0;
replace stock=regexm(desc,"emerging mar") if stock==0;
replace stock=regexm(desc,"asian eq") if stock==0;
replace stock=regexm(desc,"small cap") if stock==0;
replace stock=regexm(desc,"global disc") if stock==0;
replace stock=regexm(desc,"global tot") if stock==0;
replace stock=regexm(desc,"small-mid cap") if stock==0;
replace stock=regexm(desc,"american mutual") if stock==0;
replace stock=regexm(desc,"mkt indx") if stock==0;
replace stock=regexm(desc,"small cap") if stock==0;
replace stock=regexm(desc,"large cap") if stock==0;
replace stock=regexm(desc,"passive growth") if stock==0;
replace stock=regexm(desc,"international funds") if stock==0;
replace stock=regexm(desc,"intl fund") if stock==0;
replace stock=regexm(desc,"brokerage") if stock==0;
replace stock=regexm(desc,"growth cl") if stock==0;
replace stock=regexm(desc,"mutual invstrs cl") if stock==0;
replace stock=regexm(desc,"pacific stock") if stock==0;
replace stock=regexm(desc,"vanguard inst index") if stock==0;
replace stock=regexm(desc,"absolute return funds") if stock==0;
replace stock=regexm(desc,"ftse") if stock==0;
replace stock=regexm(desc,"traded secur") if stock==0;
replace stock=regexm(desc,"p 500") if stock==0;
replace stock=regexm(desc,"russell") if stock==0;
replace stock=regexm(desc,"stock fund") if stock==0;
replace stock=regexm(desc,"growth fund") if stock==0;
replace stock=regexm(desc,"cox stock") if stock==0;
replace stock=regexm(desc,"total stock") if stock==0;
replace stock=regexm(desc,"preferred stock") if stock==0;
replace stock=regexm(desc,"schwab") if stock==0;
replace stock=regexm(desc,"spdr") if stock==0;
replace stock=regexm(desc,"ishares core s&p") if stock==0;
replace stock=regexm(desc,"eq fund") if stock==0;
replace stock=regexm(desc,"admiral") if stock==0;
replace stock=regexm(desc,"oakmark fund") if stock==0;
replace stock=regexm(desc,"oakmark international fund") if stock==0;
replace stock=regexm(desc,"small-cap value") if stock==0;
replace stock=regexm(desc,"various securities") if stock==0;
replace stock=regexm(desc,"investment in pacific refrigeration parent") if stock==0;
replace stock=regexm(desc,"mod grwth") if stock==0;
replace stock=regexm(desc,"consrv grwth") if stock==0;
replace stock=regexm(desc,"amcap fund") if stock==0;
replace stock=regexm(desc,"t rowe price") if stock==0;
replace stock=regexm(desc,"blackrock eq") if stock==0;
replace stock=regexm(desc,"van guard") if stock==0;
replace stock=regexm(desc,"templeton developing markets") if stock==0;
replace stock=regexm(desc,"other-equity") if stock==0;
replace stock=regexm(desc,"vangurard high dividend yield index") if stock==0;
replace stock=regexm(desc,"equities") if stock==0;
replace stock=regexm(desc,"equity investments") if stock==0;
replace stock=regexm(desc,"absolute return strategy") if stock==0;
replace stock=regexm(desc,"absolute strategy") if stock==0;
replace stock=regexm(desc,"vanguard total intl stock") if stock==0;
replace stock=regexm(desc,"berkshire hathaway") if stock==0;
replace stock=regexm(desc,"marsico") if stock==0;
replace stock=regexm(desc,"highclere") if stock==0;
replace stock=regexm(desc,"investment co of america") if stock==0;
replace stock=regexm(desc,"edward jones") if stock==0;
replace stock=regexm(desc,"merrill lynch") if stock==0;
replace stock=regexm(desc,"waddell") if stock==0;
replace stock=regexm(desc,"westwood") if stock==0;
replace stock=regexm(desc,"doubleline") if stock==0;
replace stock=regexm(desc,"prudential") if stock==0;
replace stock=regexm(desc,"wisdom tree") if stock==0;
replace stock=regexm(desc,"sanderson") if stock==0;
replace stock=regexm(desc,"stifel") if stock==0;
replace stock=regexm(desc,"gateway un") if stock==0;
replace stock=regexm(desc,"pfizer") if stock==0;
replace stock=regexm(desc,"balcx") if stock==0;
replace stock=regexm(desc,"gfacx") if stock==0;
replace stock=regexm(desc,"vontobel") if stock==0  & bonds==0;
replace stock=regexm(desc,"ishares") if stock==0 & bonds==0;
replace stock=regexm(desc,"vanguard") if stock==0 & bonds==0;
replace stock=regexm(desc,"corp") if stock==0 & bonds==0;
replace stock=regexm(desc,"adr") if stock==0 & bonds==0;
replace stock=regexm(desc,"inc") if stock==0 & bonds==0;
replace stock=regexm(desc,"ltd") if stock==0 & bonds==0;
replace stock=regexm(desc,"plc") if stock==0 & bonds==0;
replace stock=regexm(desc," co") if stock==0 & bonds==0;
replace stock=regexm(desc,"shares") if stock==0 & bonds==0;
replace stock=regexm(desc,"inv cl") if stock==0 & bonds==0;
replace stock=regexm(desc,"company") if stock==0 & bonds==0;
replace stock=regexm(desc,"equity - domestic") if stock==0;
replace stock=regexm(desc,"equity - foreign") if stock==0;
replace stock=regexm(desc,"commingled funds directly holding securities") if stock==0;
replace stock=regexm(desc,"equity method investments") if stock==0;
replace stock=regexm(desc,"equity investmetns") if stock==0;
replace stock=regexm(desc,"equity mutualfunds") if stock==0;
replace stock=regexm(desc,"global asset allocation funds") if stock==0;
replace stock=regexm(desc,"federation main investment fund") if stock==0;
replace stock=regexm(desc,"wellington emerging mkt") if stock==0;
replace stock=regexm(desc,"addison clark") if stock==0;
replace stock=regexm(desc,"federated total return sers") if stock==0;
replace stock=regexm(desc,"investment in american funds - unrestricted") if stock==0;
replace stock=regexm(desc,"fpa crescent") if stock==0;
replace stock=regexm(desc,"wellington smid equity") if stock==0;
replace stock=regexm(desc,"acadian international sm cap") if stock==0;
replace stock=regexm(desc,"american funds") if stock==0;
replace stock=regexm(desc,"exchange traded & closed end funds") if stock==0;
replace stock=regexm(desc,"sterne agee portfolio - eouities") if stock==0;
replace stock=regexm(desc,"vangaurd 500 index fund") if stock==0;
replace stock=regexm(desc,"fidelity investments/brinker capital") if stock==0;
replace stock=regexm(desc,"equity-domestic") if stock==0;
replace stock=regexm(desc,"kontiki") if stock==0;
replace stock=regexm(desc,"investments-windward(unmanaged)") if stock==0;
replace stock=regexm(desc,"investments-windward(managed)") if stock==0;
replace stock=regexm(desc,"the richmond fund") if stock==0;
replace stock=regexm(desc,"investment with usmf") if stock==0;
replace stock=regexm(desc,"investments measured at nav") if stock==0;
replace stock=regexm(desc,"focused investors") if stock==0;

** SINGLE ENTRIES FOR STOCKS;
replace stock=1 if desc=="stock" & stock==0;
replace stock=1 if desc=="investments" & stock==0;
replace stock=1 if desc=="foreign equities" & stock==0;
replace stock=1 if desc=="investment portfolio" & stock==0;
replace stock=1 if desc=="absolute return" & stock==0;
replace stock=1 if desc=="fidelity" & stock==0;
replace stock=1 if desc=="stocks held for sale" & stock==0;
replace stock=1 if desc=="goldman sachs investment account" & stock==0;
replace stock=1 if desc=="wells fargo- investments" & stock==0;
replace stock=1 if desc=="vanguard account" & stock==0;
replace stock=1 if desc=="equity securities" & stock==0;
replace stock=1 if desc=="investment accounts" & stock==0;
replace stock=1 if desc=="raymond james" & stock==0;
replace stock=1 if desc=="at&t inc" & stock==0;
replace stock=1 if desc=="apple inc" & stock==0;
replace stock=1 if desc=="qualcomm inc" & stock==0;
replace stock=1 if desc=="exxon mobil corp" & stock==0;
replace stock=1 if desc=="blackstone group lp" & stock==0;
replace stock=1 if desc=="boeing co" & stock==0;
replace stock=1 if desc=="micron technology" & stock==0;
replace stock=1 if desc=="enbridge energy ptnr lp" & stock==0;
replace stock=1 if desc=="microsoft corp" & stock==0;
replace stock=1 if desc=="verizon communicatn" & stock==0;
replace stock=1 if desc=="home depot inc" & stock==0;
replace stock=1 if desc=="intel corp" & stock==0;
replace stock=1 if desc=="dowdupont inc" & stock==0;
replace stock=1 if desc=="johnson & johnson" & stock==0;
replace stock=1 if desc=="wells fargo & co" & stock==0;
replace stock=1 if desc=="jpmorgan chase & co" & stock==0;
replace stock=1 if desc=="morgan stanley" & stock==0;
replace stock=1 if desc=="caci international inc" & stock==0;
replace stock=1 if desc=="alleghany corp" & stock==0;
replace stock=1 if desc=="energizer holding inc" & stock==0;
replace stock=1 if desc=="ishares s&p pref stk indx fn" & stock==0;
replace stock=1 if desc=="kroger company" & stock==0;
replace stock=1 if desc=="kraft heinz co" & stock==0;
replace stock=1 if desc=="nokia corporation" & stock==0;
replace stock=1 if desc=="dean foods co com" & stock==0;
replace stock=1 if desc=="hershey foods corporation" & stock==0;
replace stock=1 if desc=="flowers foods inc." & stock==0;
replace stock=1 if desc=="schlumberger limited" & stock==0;
replace stock=1 if desc=="gilead sciences inc com" & stock==0;
replace stock=1 if desc=="eaton corp plc" & stock==0;
replace stock=1 if desc=="becton dickinson & co" & stock==0;
replace stock=1 if desc=="vf corp" & stock==0;
replace stock=1 if desc=="oracle corporation" & stock==0;
replace stock=1 if desc=="mcdermott intl inc" & stock==0;
replace stock=1 if desc=="75 shares berskire hathaway inc. class a" & stock==0;
replace stock=1 if desc=="investments - securities" & stock==0;
replace stock=1 if desc=="exchange-traded funds" & stock==0;
replace stock=1 if desc=="citigroup inc" & stock==0;
replace stock=1 if desc=="hp inc" & stock==0;
replace stock=1 if desc=="foot locker inc." & stock==0;
replace stock=1 if desc=="ishares msci emu" & stock==0;
replace stock=1 if desc=="investment in portfolios" & stock==0;
replace stock=1 if desc=="stocks held for sale" & stock==0;
replace stock=1 if desc=="abbvie inc" & stock==0;
replace stock=1 if desc=="opportunistic" & stock==0;
replace stock=1 if desc=="ball corp com" & stock==0;
replace stock=1 if desc=="bb&t corp" & stock==0;
replace stock=1 if desc=="bp amoco plc" & stock==0;
replace stock=1 if desc=="cvs health corp" & stock==0;
replace stock=1 if desc=="exchange - traded & closed-end funds" & stock==0;
replace stock=1 if desc=="federated investors inc cl b" & stock==0;
replace stock=1 if desc=="facebook inc -a" & stock==0;
replace stock=1 if desc=="home depot" & stock==0;
replace stock=1 if desc=="kirby corp" & stock==0;
replace stock=1 if desc=="las vegas sands corp" & stock==0;
replace stock=1 if desc=="philip morris international" & stock==0;
replace stock=1 if desc=="rayonier" & stock==0;
replace stock=1 if desc=="royal dutch shell plc-adr a" & stock==0;
replace stock=1 if desc=="sanofi -adr" & stock==0;
replace stock=1 if desc=="sealed air corp" & stock==0;
replace stock=1 if desc=="smucker j m co" & stock==0;
replace stock=1 if desc=="645,846 911 fidelity fund" & stock==0;
replace stock=1 if desc=="exchange-trade products" & stock==0;
replace stock=1 if desc=="scottrade" & stock==0;
replace stock=1 if desc=="merrill lynch acct 2000" & stock==0;
replace stock=1 if desc=="funds of funds" & stock==0;
replace stock=1 if desc=="buffalo mid cap fund #1446" & stock==0;
replace stock=1 if desc=="mobile telesystems" & stock==0;
replace stock=1 if desc=="marketable equity securities" & stock==0;
replace stock=1 if desc=="m&t investments" & stock==0;
replace stock=1 if desc=="citizens investments" & stock==0;
replace stock=1 if desc=="securities" & stock==0;
replace stock=1 if desc=="dodge and cox funds" & stock==0;
replace stock=1 if desc=="kinder morgan" & stock==0;
replace stock=1 if desc=="general elec co com" & stock==0;
replace stock=1 if desc=="dupont ei de nemours" & stock==0;
replace stock=1 if desc=="mutal funds" & stock==0;
replace stock=1 if desc=="fund of funds" & stock==0;
replace stock=1 if desc=="standard life gars 6000035 sh" & stock==0;
replace stock=1 if desc=="net asset value investments" & stock==0;
replace stock=1 if desc=="equity securities." & stock==0;
replace stock=1 if desc=="harbor capital appreciation fd" & stock==0;
replace stock=1 if desc=="am funds europacific growth fd cl f2" & stock==0;
replace stock=1 if desc=="am fds new world fd cl f2" & stock==0;
replace stock=1 if desc=="am funds europacific growth fd" & stock==0;
replace stock=1 if desc=="harbor capital appreciation fund ret" & stock==0;
replace stock=1 if desc=="am funds new world fund f-3" & stock==0;
replace stock=1 if desc=="harbor capital appreciation fund ret" & stock==0;
replace stock=1 if desc=="marathon international" & stock==0;
replace stock=1 if desc=="investment securities" & stock==0;
replace stock=1 if desc=="international securities" & stock==0;
replace stock=1 if desc=="snb investment account" & stock==0;
replace stock=1 if desc=="investments with pittsburgh fed" & stock==0;
replace stock=1 if desc=="investments - bb&t" & stock==0;
replace stock=1 if desc=="trowe price balanced fund" & stock==0;
replace stock=1 if desc=="blackrock funds" & stock==0;
replace stock=1 if desc=="chi oip - equity securities" & stock==0;
replace stock=1 if desc=="american beacon fds ambbway lc instl" & stock==0;
replace stock=1 if desc=="laudis intl mktmasters" & stock==0;
replace stock=1 if desc=="investment securities - reserve" & stock==0;
replace stock=1 if desc=="john hancock disciplined value international fund class i" & stock==0;
replace stock=1 if desc=="gateway fund" & stock==0;
replace stock=1 if desc=="blackrock global fund" & stock==0;
replace stock=1 if desc=="equity-cfgm" & stock==0;
replace stock=1 if desc=="ts&w" & stock==0;
replace stock=1 if desc=="gs tactical tilt" & stock==0;
replace stock=regexm(desc,"acadian international small-cap fund") if stock==0;
replace stock=regexm(desc,"advisory research sm/mid cap") if stock==0;
replace stock=regexm(desc,"cf winton futures fd - lead ser fd") if stock==0;
replace stock=regexm(desc,"standard lilfe investments global") if stock==0;
replace stock=regexm(desc,"cf the winton fund") if stock==0;
replace stock=regexm(desc,"gotham enhanced long fund") if stock==0;
replace stock=regexm(desc,"franklin templeton") if stock==0;
replace stock=regexm(desc,"brown adv total return fund") if stock==0;
replace stock=regexm(desc,"metropolitan west") if stock==0;

** COMMODITY;
gen commodity=regexm(desc,"commodit");
replace commodity=regexm(desc,"mineral") if commodity==0;
replace commodity=regexm(desc,"natural res") if commodity==0;
replace commodity=regexm(desc,"timber") if commodity==0;
replace commodity=regexm(desc,"gold") if commodity==0;
replace commodity=regexm(desc,"gravel") if commodity==0;
replace commodity=regexm(desc,"energy") if commodity==0;
replace commodity=regexm(desc,"oil and gas") if commodity==0;
replace commodity=regexm(desc,"coal reserves") if commodity==0;
replace commodity=regexm(desc,"nikkei - ngs") if commodity==0;
replace commodity=1 if desc=="eig" & commodity==0;
replace commodity=regexm(desc,"coal, oil & gas reserves") if commodity==0;

** DERIVATIVES;
gen deriv=regexm(desc,"derivat");
replace deriv=regexm(desc,"hedging") if deriv==0;
replace deriv=regexm(desc,"hedges") if deriv==0;
replace deriv=regexm(desc,"cds") if deriv==0;
replace deriv=regexm(desc,"denvatives") if deriv==0;
replace deriv=regexm(desc,"put options") if deriv==0;
replace deriv=regexm(desc,"mortality arbitrage") if deriv==0;
replace deriv=regexm(desc,"swaps") if deriv==0;
replace deriv=regexm(desc,"directional hedge") if deriv==0;
replace deriv=regexm(desc,"convexity") if deriv==0;
replace deriv=regexm(desc,"arbitrage") if deriv==0;

** CURRENCY;
gen currency=regexm(desc,"usd");
replace currency=regexm(desc,"coin") if currency==0;

** REAL ESTATE;
gen real_estate=regexm(desc,"real assets");
replace real_estate=regexm(desc,"real asset funds") if real_estate==0;
replace real_estate=regexm(desc,"estate") if real_estate==0;
replace real_estate=regexm(desc,"reit") if real_estate==0;
replace real_estate=regexm(desc,"lease") if real_estate==0;
replace real_estate=regexm(desc,"mortgage") if real_estate==0;
replace real_estate=regexm(desc,"propert") if real_estate==0;
replace real_estate=regexm(desc,"mortgage_backed") if real_estate==0;
replace real_estate=regexm(desc,"realty") if real_estate==0;
replace real_estate=regexm(desc,"rlty") if real_estate==0;
replace real_estate=regexm(desc,"rel est") if real_estate==0;
replace real_estate=regexm(desc,"real est") if real_estate==0;
replace real_estate=regexm(desc,"infrastru") if real_estate==0;
replace real_estate=regexm(desc,"land") if real_estate==0;
replace real_estate=regexm(desc,"tenn valley") if real_estate==0;
replace real_estate=regexm(desc,"fed farm") if real_estate==0;
replace real_estate=regexm(desc,"hotel") if real_estate==0;
replace real_estate=regexm(desc,"investments - tangible assets") if real_estate==0;
replace real_estate=1 if desc=="real est.invest.trusts" & real_estate==0;
replace real_estate=regexm(desc,"real asset securities") if real_estate==0;
replace real_estate=regexm(desc,"darlington") if real_estate==0;
replace real_estate=regexm(desc,"hard assets") if real_estate==0;
replace real_estate=regexm(desc,"commercial securites") if real_estate==0;
replace real_estate=regexm(desc,"development rights") if real_estate==0;
replace real_estate=regexm(desc,"mower crt") if real_estate==0;

** CASH;
gen cash=regexm(desc,"cash");
replace cash=regexm(desc,"money mar") if cash==0;
replace cash=regexm(desc,"flexible capital") if cash==0;
replace cash=regexm(desc,"money mkt") if cash==0;
replace cash=regexm(desc,"marketable secur") if cash==0;
replace cash=regexm(desc,"marketable inv") if cash==0;
replace cash=regexm(desc,"liquid reserves") if cash==0;
replace cash=regexm(desc,"other short-term") if cash==0;
replace cash=regexm(desc,"savings") if cash==0;
replace cash=regexm(desc,"due from fund managers") if cash==0;
replace cash=regexm(desc,"broker receivable") if cash==0;
replace cash=regexm(desc,"receivables") if cash==0;
replace cash=regexm(desc,"transfers") if cash==0;
replace cash=regexm(desc,"checks in trans") if cash==0;
replace cash=1 if desc=="webster financial investment accou" & cash==0;
replace cash=1 if desc=="checking pool";
replace cash=1 if desc=="faro invest rpg950278";
replace cash=1 if desc=="wells fargo #7408-7065";
replace cash=1 if desc=="tejas #b3s-662870";
replace cash=1 if desc=="ubs";

** TRUSTS & BANKS;
gen trusts=regexm(desc,"trust");
replace trusts=regexm(desc,"common trust") if trusts==0;
replace trusts=regexm(desc,"remainder trust") if trusts==0;
replace trusts=regexm(desc,"bank") if trusts==0;
replace trusts=regexm(desc,"deposit") if trusts==0;
replace trusts=regexm(desc,"certificates of dep") if trusts==0;
replace trusts=regexm(desc,"jp morgan cd") if trusts==0;
replace trusts=regexm(desc,"umpqua holdings corp") if trusts==0;
replace trusts=regexm(desc,"wells fargo") if trusts==0;
replace trusts=regexm(desc,"wachovia") if trusts==0;
replace trusts=regexm(desc,"ubs ") if trusts==0;
replace trusts=regexm(desc,"regions morgan keegan") if trusts==0;
replace trusts=regexm(desc,"custody") if trusts==0;
replace trusts=regexm(desc,"hillard lyons") if trusts==0;
** NOTE: did some searching these are all indian bank accounts;
replace trusts=regexm(desc,"rj elite") if trusts==0;
replace trusts=regexm(desc,"rj jahagirdar") if trusts==0;
replace trusts=regexm(desc,"rj khedkar") if trusts==0;
replace trusts=regexm(desc,"rj deshpande") if trusts==0;
replace trusts=regexm(desc,"rj tulshibagwale") if trusts==0;
replace trusts=regexm(desc,"agency account") if trusts==0;
replace trusts=regexm(desc,"planned gi") if trusts==0;
replace trusts=regexm(desc,"gift") if trusts==0;
replace trusts=regexm(desc,"donor advised funds") if trusts==0;
replace trusts=regexm(desc,"held for other") if trusts==0;

** ALTERNATIVE INVESTMENTS;
gen alt_inv=regexm(desc,"alternative");
replace alt_inv=regexm(desc,"alt invest") if alt_inv==0;
replace alt_inv=regexm(desc,"other invest") if alt_inv==0;
replace alt_inv=regexm(desc,"mkt alt") if alt_inv==0;
replace alt_inv=regexm(desc,"diversif") if alt_inv==0;
replace alt_inv=regexm(desc,"funds-other") if alt_inv==0;
replace alt_inv=1 if desc=="other" & alt_inv==0;;
replace alt_inv=1 if desc=="other." & alt_inv==0;;
replace alt_inv=1 if desc=="offshore investments" & alt_inv==0;;
replace alt_inv=1 if desc=="other securities" & alt_inv==0;;
replace alt_inv=1 if desc=="assets limited" & alt_inv==0;
replace alt_inv=1 if desc=="art" & alt_inv==0;
replace alt_inv=regexm(desc,"investments - other") if alt_inv==0;
replace alt_inv=regexm(desc,"powershares variable") if alt_inv==0;
replace alt_inv=regexm(desc,"investments-oth") if alt_inv==0;
replace alt_inv=regexm(desc,"securities - not actively traded") if alt_inv==0;
replace alt_inv=regexm(desc,"not readily mktbl securities") if alt_inv==0;
replace alt_inv=regexm(desc,"aircraft") if alt_inv==0;
replace alt_inv=regexm(desc,"miscellaneous inv") if alt_inv==0;
replace alt_inv=regexm(desc,"varde fund") if alt_inv==0;
replace alt_inv=regexm(desc,"contingent asset portfolio") if alt_inv==0;
replace alt_inv=regexm(desc,"miscellaneous") if alt_inv==0;
replace alt_inv=regexm(desc,"tracking unit") if alt_inv==0;
replace alt_inv=regexm(desc,"other assets") if alt_inv==0;
replace alt_inv=regexm(desc,"other capital assets") if alt_inv==0;

** POOLED LONG-TERM INVESTMENTS OFTEN SEPARATE FROM EQUITY, BOND, ETC...;
** E.g., this is the JCEP (jewish comm.) endowment, etc.;
gen pool_inv=regexm(desc,"pool");
replace pool_inv=regexm(desc,"endowment") if pool_inv==0;
replace pool_inv=regexm(desc,"sei ") if pool_inv==0;
replace pool_inv=regexm(desc,"jcep") if pool_inv==0;
replace pool_inv=regexm(desc,"jfmc investment portfolio") if pool_inv==0;
replace pool_inv=regexm(desc,"cap group -m&m simon") if pool_inv==0;
replace pool_inv=regexm(desc,"church") if pool_inv==0;
replace pool_inv=regexm(desc,"israel") if pool_inv==0;
replace pool_inv=regexm(desc,"episcopal") if pool_inv==0;
replace pool_inv=regexm(desc,"university") if pool_inv==0;
replace pool_inv=regexm(desc,"long term") if pool_inv==0;
replace pool_inv=regexm(desc,"long-term") if pool_inv==0;
replace pool_inv=regexm(desc,"collective funds") if pool_inv==0;
replace pool_inv=regexm(desc,"comingled investments") if pool_inv==0;
replace pool_inv=regexm(desc,"co-mingled funds") if pool_inv==0;
replace pool_inv=regexm(desc,"commingled funds") if pool_inv==0;
replace pool_inv=regexm(desc,"commingled fund-balanced") if pool_inv==0;
replace pool_inv=regexm(desc,"commingled fund investment") if pool_inv==0;
replace pool_inv=regexm(desc,"collective funds") if pool_inv==0;
replace pool_inv=regexm(desc,"absolute returns funds") if pool_inv==0;
replace pool_inv=regexm(desc,"global asset allocation") if pool_inv==0;
replace pool_inv=regexm(desc,"pension") if pool_inv==0;
replace pool_inv=regexm(desc,"sli global absolute return") if pool_inv==0;
replace pool_inv=regexm(desc,"investment contributions in advance") if pool_inv==0;
replace pool_inv=regexm(desc,"investment account - cap camp account") if pool_inv==0;
replace pool_inv=regexm(desc,"tiff ") if pool_inv==0;
replace pool_inv=regexm(desc,"pooled investments") if pool_inv==0;
replace pool_inv=regexm(desc,"pooled") if pool_inv==0;
replace pool_inv=regexm(desc,"board designated") if pool_inv==0;
replace pool_inv=regexm(desc,"central endowmwnt fund") if pool_inv==0;
replace pool_inv=regexm(desc,"assets related to fiduciary agreements") if pool_inv==0;
replace pool_inv=regexm(desc,"investment in american funds - endowed") if pool_inv==0;
replace pool_inv=regexm(desc,"fjc - executive dir discretionary fund") if pool_inv==0;


** Trying to figure out what remains to be classified -- need to classify these remaining 400 or so;
egen is_classified=rowtotal(impact hfund bonds pe vc stock alt_inv commodity deriv currency real_estate cash trusts pool_inv);
tab is_classified, m;
replace stock=1 if is_classified==0;
drop is_classified;
egen is_classified=rowtotal(impact hfund bonds pe vc stock alt_inv commodity deriv currency real_estate cash trusts pool_inv);
replace stock=0 if is_classified>1 & stock==1;
drop is_classified;
egen is_classified=rowtotal(impact hfund bonds pe vc stock alt_inv commodity deriv currency real_estate cash trusts pool_inv);
tab is_classified, m;
gen sec_value_new=sec_value;
replace sec_value_new=sec_value/is_classified if is_classified>1;
sum sec_value_new, d;
format sec_value_new %12.0f;
sum sec_value_new, d;

** FOR SOME REGRESSIONS IT WILL BE IMPORTANT TO UNDERSTAND SHORT VS LONG TERM;
gen short_term=regexm(desc,"short");
replace short_term=1 if regexm(desc,"marketable");
replace short_term=1 if regexm(desc,"market able");
replace short_term=1 if regexm(desc,"money mkt");
replace short_term=1 if regexm(desc,"money market");
gen long_term=regexm(desc,"long");
replace long_term=1 if regexm(desc,"patient");
replace long_term=1 if regexm(desc,"illiquid");
replace long_term=1 if regexm(desc,"sustain");
replace long_term=1 if regexm(desc,"angel");
replace long_term=1 if regexm(desc,"not active");


** There are a few long/short hedge funds etc... want to remove those;
gen longshort=regexm(desc,"long/short");
replace longshort=1 if regexm(desc,"long / short");
replace longshort=1 if regexm(desc,"long short");
tab longshort, m;
replace short_term=0 if longshort==1;
** The long-short hedge funds are long-term investments;
replace long_term=1 if longshort==1;
drop longshort;

** GETTING THE VALUES OF IT ALL;
** THIS IS THE TOTAL VALUE OF SEC SECURITIES FOR A GIVEN DAF IN A YEAR;
bys ein year: egen double tot_val=total(sec_value);
format tot_val %12.0fc;
gen double impact_val=sec_value_new if impact==1;
gen double hfund_val=sec_value_new if hfund==1;
gen double fi_val=sec_value_new if bonds==1;
gen double vc_val=sec_value_new if vc==1;
gen double pe_val=sec_value_new if pe==1;
gen double eqty_val=sec_value_new if stock==1;
gen double alt_inv_val=sec_value_new if alt_inv==1;
gen double commod_val=sec_value_new if commodity==1;
gen double deriv_val=sec_value_new if deriv==1;
gen double curr_val=sec_value_new if currency==1;
gen double real_val=sec_value_new if real_estate==1;
gen double cash_val=sec_value_new if cash==1;
gen double trust_val=sec_value_new if trust==1;
gen double pooled_val=sec_value_new if pool_inv==1;

foreach x in impact_val hfund_val fi_val vc_val pe_val eqty_val alt_inv_val commod_val 
deriv_val curr_val real_val cash_val trust_val pooled_val   {;
	replace `x'=0 if `x'==.;
};

** CREATING LONG TERM VS. SHORT-TERM MEASURES;
** NOTE: using the original sec_value if any of it is classified as short-term;
gen double st_val=0;
replace st_val=sec_value if short_term==1;
** This alternative one uses the divided up stuff;
gen double rel_st_eqty=0;
gen double rel_st_pool=0;
gen double rel_st_alt_inv=0;
gen double rel_st_fi=0;
** Assuming most equity is short-term;
replace rel_st_eqty=eqty_val if long_term!=1 & eqty_val!=0;
** Assuming pooled assets are typically long-term unless specified;
replace rel_st_pool=pooled_val if short_term==1 & pooled_val!=0;
** Assuming most alt investments are long-term unless specified;
replace rel_st_alt_inv=alt_inv_val if short_term==1 & alt_inv_val!=0;
** Assuming most bonds are long-term unless specified; 
replace rel_st_fi=fi_val if short_term==1 & fi_val!=0;
** In combination cash, trusts, currency, derivates are short-term plus most equities, and small portion of bonds, pooled, and alt investments;
gen double st_val_alt=cash_val+trust_val+curr_val+deriv_val+rel_st_eqty+rel_st_fi+rel_st_pool+rel_st_alt_inv;

** NOTE: using the original sec_value if any of it is classified as long-term;
gen double lt_val=0;
replace lt_val=sec_value if long_term==1;
gen double rel_lt_eqty=0;
gen double rel_lt_pool=0;
gen double rel_lt_commod=0;
gen double rel_lt_fi=0;
gen double rel_lt_hfund=0;
gen double rel_lt_alt_val_inv=0;
replace rel_lt_eqty=eqty_val if long_term==1 & eqty_val!=0;
replace rel_lt_pool=pooled_val if short_term!=1 & pooled_val!=0;
replace rel_lt_commod=commod_val if short_term!=1 & commod_val!=0;
replace rel_lt_fi=fi_val if short_term!=1 & fi_val!=0;
replace rel_lt_hfund=hfund_val if short_term!=1 & hfund_val!=0;
replace rel_lt_alt_val_inv=alt_inv_val if short_term!=1 & alt_inv_val!=0;
gen double lt_val_alt=vc_val+pe_val+impact_val+real_val+rel_lt_eqty+rel_lt_pool+rel_lt_commod+rel_lt_fi+rel_lt_hfund+rel_lt_alt_val_inv;
** NOTE: again using the full value here;
gen double not_st_lt_val_alt=sec_value-(lt_val_alt+st_val_alt);
replace lt_val_alt=lt_val_alt+not_st_lt_val_alt if not_st_lt_val_alt!=0;
** It just classifies far too few securities;
drop st_val lt_val;

foreach x in st_val_alt lt_val_alt not_st_lt_val_alt {;
	replace `x'=0 if `x'==.;
};
rename st_val_alt st_val;
rename lt_val_alt lt_val;

** FINALLY WE ALSO WANT TO DO THE CLASSIFICATION FROM LERNER, SCHOAR, WANG (2008 JEP);
gen double lsw_cash=cash_val+curr_val+trust_val;
gen double lsw_alt=impact_val+hfund_val+pe_val+vc_val+commod_val+deriv_val+alt_inv_val;
gen double lsw_eqty=eqty_val+pooled_val;
gen double lsw_fi=fi_val;
gen double lsw_real=real_val;
gen double lsw_all=cash_val+curr_val+trust_val+impact_val+hfund_val+pe_val+vc_val+commod_val+deriv_val+alt_inv_val+eqty_val+pooled_val+fi_val+real_val;
save "Securities with handcode details.dta", replace;

sort ein year;
** Recall tot_val is already the modified total value based on the allocation to different categories when they are combined;
collapse (mean) tot_val (sum) lsw_* impact_val hfund_val fi_val vc_val pe_val eqty_val alt_inv_val commod_val deriv_val 
curr_val real_val cash_val trust_val pooled_val st_val lt_val, by(ein year);
gen diff=abs(tot_val-lsw_all);
sum diff, d;
gen tot_val_ex_cash=tot_val-cash_val;
foreach x in lsw_cash lsw_alt lsw_eqty lsw_fi lsw_real lsw_all impact_val hfund_val fi_val vc_val pe_val eqty_val alt_inv_val commod_val deriv_val 
curr_val real_val cash_val trust_val pooled_val tot_val st_val lt_val tot_val_ex_cash {;
format `x' %12.0f;
gen p`x'=`x'/tot_val;
bys ein: gen ret_`x'=ln(`x'/`x'[_n-1]) if year-1==year[_n-1];
};
save "Securities with handcode for tables.dta", replace;
log close;
